##############################################################################
############################## Section 4 Credit contracts & Appendix 
############################## Shares by loan type - all banks, only main banks, all excluding main banks
##############################################################################

# import libraries
import os
import pandas as pd
import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from matplotlib.colors import ListedColormap

path_data = r'P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final datasets'
path_charts = r'P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Credit quantity\by loan type\final'
if not os.path.isdir(path_charts):
    os.makedirs(path_charts)


# define plotting function
def plot_loantype_shr(data, color, order):
    # data should be a dataframe
    # color should be a text
    # order should be a list of lists, where the first is order of all countries and the second is selected countries

    # set parameters
    sns.set(rc={'figure.figsize':(15,10), 'axes.labelsize':26, 'axes.titlesize':26,  'axes.labelpad':15, 'xtick.labelsize':26, 'ytick.labelsize':26}, style='white')
    
    # all banks
    # selected countries
    plt.figure()
    data[0].set_index('CNTRY').loc[order[1]].plot(kind='bar', stacked=True, colormap=ListedColormap(sns.color_palette(color,n_colors=9)))
    plt.xlabel('Country', labelpad=6)
    plt.ylabel('Outstanding nominal amount share', labelpad=6)
    plt.xticks(rotation=0)
    p = plt.legend(bbox_to_anchor=(1.20,0), borderaxespad=3.5, ncol=5, prop={'size': 20}, frameon=True, shadow=True)
    plt.savefig(path_charts + '\\Section4_loanshr_bytype_201912.pdf', bbox_extra_artists=(p,), bbox_inches='tight', dpi=600)
    
    # one chart with both total shares excl. MB and shares from main bank
    # only selected countries    
    ticks = np.arange(0.6, 11.6, 1).tolist()
    fig, ax = plt.subplots()
    # first stacked bar - totals excl. mb
    data[2].set_index('CNTRY').loc[order[1]].plot(ax=ax, kind='bar', stacked=True, width=0.35, position=-0.5, colormap=ListedColormap(sns.color_palette(color,n_colors=9)))
    # second stacked bar - mb
    data[1].set_index('CNTRY').loc[order[1]].plot(ax=ax, kind='bar', stacked=True, width=0.35, position=-1.5, colormap=ListedColormap(sns.color_palette(color,n_colors=9)))
    plt.xlabel('Country', labelpad=6)
    plt.ylabel('Outstanding nominal amount share', labelpad=6)
    plt.xticks(rotation=0, ticks=ticks)
    handles, labels = plt.gca().get_legend_handles_labels()
    by_label = dict(zip(labels, handles))
    p = plt.legend(by_label.values(), by_label.keys(), bbox_to_anchor=(1.20,0), borderaxespad=3.5, ncol=5, prop={'size': 20}, frameon=True, shadow=True)
    plt.savefig(path_charts + '\\Appendix_loanshr_mixed_bytype_201912.pdf', bbox_extra_artists=(p,), bbox_inches='tight', dpi=600)    

    
# import dataset and apply filters
# it includes also the creditors
AC_final = pd.read_stata(path_data + '\instr_lvl_mb_dt_f.dta')
AC_final.rename(columns={'cntry_dbtr':'CNTRY','nace_code':'NACE_CODE', 'typ_instr_corr':'TYP_INSTR_CORR', 'mb_flag':'MB_FLAG', 'outst_amnt_byinstr':'ONA_DBTR_LOAN','mb_ona_loan':'MB_ONA_LOAN' }, inplace=True)
AC_final.drop(AC_final[AC_final.NACE_CODE == ''].index, inplace=True)
AC_final.drop(AC_final[AC_final.NACE_CODE.isna() == True].index, inplace=True)
ea11 = ['AT','BE','DE','ES','FI','FR','GR','IE','IT','NL','PT']
AC_final.drop(AC_final[AC_final.CNTRY.isin(ea11) == False].index, inplace=True)

# transform dataset
# we need the individual shares on debtor level divided by the number of debtors in a country 
ona_dbtr = AC_final[['CNTRY', 'dbtr_id','TYP_INSTR_CORR','ONA_DBTR_LOAN']].drop_duplicates().groupby(['CNTRY', 'dbtr_id'])['ONA_DBTR_LOAN'].sum().reset_index().rename(columns={'ONA_DBTR_LOAN':'ONA_DBTR'})
instr_lvl = AC_final[['CNTRY', 'dbtr_id','TYP_INSTR_CORR','ONA_DBTR_LOAN']].drop_duplicates().merge(ona_dbtr,how='left',on=['CNTRY', 'dbtr_id'])
instr_lvl['loan_shr'] = instr_lvl['ONA_DBTR_LOAN']/instr_lvl['ONA_DBTR']
n_dbtrs = AC_final[['CNTRY', 'dbtr_id']].drop_duplicates().groupby(['CNTRY'])['dbtr_id'].count().reset_index().rename(columns={'dbtr_id':'n_dbtrs'})
ona_bytype = instr_lvl.groupby(['CNTRY','TYP_INSTR_CORR'])['loan_shr'].sum().reset_index().rename(columns={'loan_shr':'loans_bycntry'})
gr_all = ona_bytype.merge(n_dbtrs, how='left', on=['CNTRY'])
gr_all['SHR_BY_TYPE'] = gr_all['loans_bycntry']/gr_all['n_dbtrs']

# credit from main bank 
# we need the individual MB loans by instrument type for a debtor - MB_ONA_LOAN
# we need the total loan from the main bank - outst_amnt_ttl_max
mb = AC_final[AC_final.MB_FLAG == 1].copy()
crdtrs = mb[['CNTRY', 'dbtr_id','crdtr_id']].drop_duplicates()
crdtrs['num_in_group'] = crdtrs.groupby(['CNTRY', 'dbtr_id']).cumcount()
mb = mb.merge(crdtrs,how='left',on=['CNTRY', 'dbtr_id','crdtr_id'])
# calculate mb shares
mb_dt = mb[mb.num_in_group == 0].copy()
mb_dt['loan_shr_instr'] = mb_dt['MB_ONA_LOAN']/mb_dt['outst_amnt_ttl_max']
mb_shr = mb_dt.groupby(['CNTRY', 'TYP_INSTR_CORR'])['loan_shr_instr'].sum().reset_index().rename(columns={'loan_shr_instr':'loans_bycntry'})
gr_mb_all = mb_shr.merge(n_dbtrs, how='left', on=['CNTRY'])
gr_mb_all['SHR_BY_TYPE'] = gr_mb_all['loans_bycntry']/gr_mb_all['n_dbtrs']
print(gr_mb_all.groupby(['CNTRY'])['SHR_BY_TYPE'].sum())

# credit from all other banks  
# we need the excluded MB rows and include the creditors that are not counted as main bank
# we need the individual loans by instrument type for a debtor - outst_amnt_crdtr_loan
# we need to create the total loans excluding the main bank - ona_sum_exclmb
exclmb = pd.concat([AC_final[AC_final.MB_FLAG == 0],mb[mb.num_in_group != 0]], axis=0)
n_dbtrs_exclmb = exclmb[['CNTRY', 'dbtr_id']].drop_duplicates().groupby(['CNTRY'])['dbtr_id'].count().reset_index().rename(columns={'dbtr_id':'n_dbtrs'})
# calculate total credit
ona_exclmb = exclmb.groupby(['CNTRY','dbtr_id'])['outst_amnt_crdtr_loan'].sum().reset_index().rename(columns={'outst_amnt_crdtr_loan':'ona_sum_exclmb'})
exclmb = exclmb.merge(ona_exclmb,how='left',on=['CNTRY','dbtr_id'])
exclmb['loan_shr_instr'] = exclmb['outst_amnt_crdtr_loan']/exclmb['ona_sum_exclmb']
exclmb_shr = exclmb.groupby(['CNTRY', 'TYP_INSTR_CORR'])['loan_shr_instr'].sum().reset_index().rename(columns={'loan_shr_instr':'loans_bycntry'})
gr_exclmb_all = exclmb_shr.merge(n_dbtrs_exclmb, how='left', on=['CNTRY'])
gr_exclmb_all['SHR_BY_TYPE'] = gr_exclmb_all['loans_bycntry']/gr_exclmb_all['n_dbtrs']
print(gr_exclmb_all.groupby(['CNTRY'])['SHR_BY_TYPE'].sum())

# reshape
all_reshaped = gr_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').reset_index().fillna(0)   
all_mb_reshaped = gr_mb_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').reset_index().fillna(0)
all_exclmb_reshaped = gr_exclmb_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').reset_index().fillna(0)
# rearrange columns
column_order = ['CNTRY', 'Loans', 'Credit lines', 'Finance leases', 'Trade receivables', 'Revolving credit']
all_reshaped = all_reshaped[column_order]
all_mb_reshaped = all_mb_reshaped[column_order]
all_exclmb_reshaped = all_exclmb_reshaped[column_order]
column_names = ['CNTRY', 'Loans', 'Credit lines', 'Finance leases', 'Trade receivables', 'Revolving credit']
all_reshaped.columns = column_names
all_mb_reshaped.columns = column_names
all_exclmb_reshaped.columns = column_names

# country order
order_cntry_alph = ['AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK']
sel_countries_alph = ['AT','BE','DE','ES','FI','FR','GR','IE','IT','NL','PT']
# order both by loans + credit lines of all
order = gr_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').fillna(0)
order['order'] = order['Loans'] + order['Credit lines']
order_all = order.sort_values(by='order', ascending=False).index
order_sel = order.loc[sel_countries_alph,:].sort_values(by='order', ascending=False).index

# plot
plot_loantype_shr(data=[all_reshaped,all_mb_reshaped,all_exclmb_reshaped], color='Spectral', order=[order_all, order_sel])  

# calculate shares by size
sz_dict = {}
column_order = ['CNTRY', 'Loans', 'Credit lines', 'Finance leases', 'Trade receivables', 'Revolving credit']
column_names = ['CNTRY', 'Loans', 'Credit lines', 'Finance leases', 'Trade receivables', 'Revolving credit']
# country order
order_cntry_alph = ['AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK']
sel_countries_alph = ['AT','BE','DE','ES','FI','FR','GR','IE','IT','NL','PT']

for i in AC_final.sz_f.unique():
    # select data and calculate shares
    t = AC_final[AC_final.sz_f == i][['CNTRY', 'dbtr_id','TYP_INSTR_CORR','ONA_DBTR_LOAN']].drop_duplicates().groupby(['CNTRY', 'dbtr_id'])['ONA_DBTR_LOAN'].sum().reset_index().rename(columns={'ONA_DBTR_LOAN':'ONA_DBTR'})
    t_all = AC_final[AC_final.sz_f == i][['CNTRY', 'dbtr_id','TYP_INSTR_CORR','ONA_DBTR_LOAN']].drop_duplicates().merge(ona_dbtr,how='left',on=['CNTRY', 'dbtr_id'])
    t_all['loan_shr'] = t_all['ONA_DBTR_LOAN']/t_all['ONA_DBTR']
    n_db = AC_final[AC_final.sz_f == i][['CNTRY', 'dbtr_id']].drop_duplicates().groupby(['CNTRY'])['dbtr_id'].count().reset_index().rename(columns={'dbtr_id':'n_dbtrs'})
    t_bytype = t_all.groupby(['CNTRY','TYP_INSTR_CORR'])['loan_shr'].sum().reset_index().rename(columns={'loan_shr':'loans_bycntry'})
    gr_all = t_bytype.merge(n_db, how='left', on=['CNTRY'])
    gr_all['SHR_BY_TYPE'] = gr_all['loans_bycntry']/gr_all['n_dbtrs']
    # reshape
    all_reshaped = gr_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').reset_index().fillna(0)   
    all_reshaped = all_reshaped[column_order]
    all_reshaped.columns = column_names
    # order by large firms
    order = gr_all.pivot(index='CNTRY', columns='TYP_INSTR_CORR', values='SHR_BY_TYPE').fillna(0)
    order['order'] = order['Loans'] + order['Credit lines']
    order_all = order.sort_values(by='order', ascending=False).index
    order_sel = order_all[order_all.isin(sel_countries_alph)]
    # save
    sz_dict[i] = [gr_all,all_reshaped,order_sel]

# plot
sns.set(rc={'figure.figsize':(15,10), 'axes.labelsize':26, 'axes.titlesize':26,  'axes.labelpad':15, 'xtick.labelsize':26, 'ytick.labelsize':26}, style='white')
sz_d = {1.0:'Large',2.0:'Medium',3.0:'Small',4.0:'Micro'}
for i in sz_d.keys():
    print (i)
    fig = plt.figure()
    # only one stacked bar - order by large firms
    sz_dict[i][1].set_index('CNTRY').loc[sz_dict[1.0][2]].plot(kind='bar', stacked=True, colormap=ListedColormap(sns.color_palette('Spectral',n_colors=9)))
    plt.xlabel('Country', labelpad=6)
    plt.ylabel('Outstanding nominal amount share', labelpad=6)
    plt.xticks(rotation=0)
    handles, labels = plt.gca().get_legend_handles_labels()
    by_label = dict(zip(labels, handles))
    p = plt.legend(by_label.values(), by_label.keys(), bbox_to_anchor=(1.20,0), borderaxespad=3.5, ncol=5, prop={'size': 20}, frameon=True, shadow=True)
    plt.savefig(path_charts + '\\Appendix_loanshr_bysz_'+str(sz_d[i])+'_201912.pdf', bbox_extra_artists=(p,), bbox_inches='tight', dpi=600)



